package com.tianqu.tms.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.tianqu.tms.core.exception.TmsDaoException;
import com.tianqu.tms.dto.CheckPadPicDto;

public class CheckPadPicDaoImp extends TmsDao implements CheckPadPicDao {

	protected CheckPadPicDaoImp() throws TmsDaoException {
		super();
		
	}
	// 查询车辆图片信息功能
		@Override
		public CheckPadPicDto getCarCheckPad(String licensePlate, String Vin)throws TmsDaoException {
			PreparedStatement ps = null;
			ResultSet rs = null;
			CheckPadPicDto dto = new CheckPadPicDto();
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT ");
			sb.append(" ID,");
			sb.append(" LICENSEPLATE,");
			sb.append(" VIN,");
			sb.append(" CARDTYPE,");
			sb.append(" CARIMAGES1,");
			sb.append(" CARIMAGES2,");
			sb.append(" CARIMAGES3,");
			sb.append(" CARIMAGES4,");
			sb.append(" STATUS,");
			sb.append(" INSERTTIME,");
			sb.append(" INSERTUSERID,");
			sb.append(" UPDATETIME,");
			sb.append(" UPDATEUSERID");
			sb.append(" FROM ");
			sb.append(TmsTableNames.TMS_CHECK_PAD_PIC);
			sb.append(" WHERE ");
			sb.append(" STATUS = 1 ");
			sb.append(" AND LICENSEPLATE=?");
			sb.append(" AND Vin=?");
			
			try {
				ps = conn.prepareStatement(sb.toString());
				ps.setString(1, licensePlate);
				ps.setString(2, Vin);
				rs = ps.executeQuery();
				while(rs.next()){
					dto.setId(rs.getLong("ID"));
					dto.setLicensePlate(rs.getString("LICENSEPLATE"));
					dto.setVin(rs.getString("VIN"));
					dto.setCardType(rs.getInt("CARDTYPE"));
					dto.setCarImage1(rs.getString("CARIMAGES1"));
					dto.setCarImage2(rs.getString("CARIMAGES2"));
					dto.setCarImage3(rs.getString("CARIMAGES3"));
					dto.setCarImage4(rs.getString("CARIMAGES4"));
					dto.setStatus(rs.getInt("STATUS"));
					dto.setInsertTime(rs.getTimestamp("INSERTTIME"));
					dto.setInsertUserId(rs.getInt("INSERTUSERID"));
					dto.setUpdateTime(rs.getTimestamp("UPDATETIME"));
					dto.setUpdateUserId(rs.getInt("UPDATEUSERID"));
					
				}
				
			}  catch (SQLException e) {
				throw new TmsDaoException(e.toString());

			} finally {
				super.close(rs, ps);
			}
			return dto;
		}
	@Override
	public CheckPadPicDto findDtoByLicensePlate(String licensePlate) throws TmsDaoException{
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		CheckPadPicDto dto = new CheckPadPicDto();
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(" ID,");
		sb.append(" LICENSEPLATE,");
		sb.append(" VIN,");
		sb.append(" CARDTYPE,");
		sb.append(" CARIMAGES1,");
		sb.append(" CARIMAGES2,");
		sb.append(" CARIMAGES3,");
		sb.append(" CARIMAGES4,");
		sb.append(" STATUS,");
		sb.append(" INSERTTIME,");
		sb.append(" INSERTUSERID,");
		sb.append(" UPDATETIME,");
		sb.append(" UPDATEUSERID");
		sb.append(" FROM ");
		sb.append(TmsTableNames.TMS_CHECK_PAD_PIC);
		sb.append(" WHERE ");
		sb.append(" STATUS = 1 ");
		sb.append(" AND LICENSEPLATE="+'"'+licensePlate+'"');
		
		try {
			ps = conn.prepareStatement(sb.toString());
			rs = ps.executeQuery();
			while(rs.next()){
				dto.setId(rs.getLong("ID"));
				dto.setLicensePlate(rs.getString("LICENSEPLATE"));
				dto.setVin(rs.getString("VIN"));
				dto.setCardType(rs.getInt("CARDTYPE"));
				dto.setCarImage1(rs.getString("CARIMAGES1"));
				dto.setCarImage2(rs.getString("CARIMAGES2"));
				dto.setCarImage3(rs.getString("CARIMAGES3"));
				dto.setCarImage4(rs.getString("CARIMAGES4"));
				dto.setStatus(rs.getInt("STATUS"));
				dto.setInsertTime(rs.getTimestamp("INSERTTIME"));
				dto.setInsertUserId(rs.getInt("INSERTUSERID"));
				dto.setUpdateTime(rs.getTimestamp("UPDATETIME"));
				dto.setUpdateUserId(rs.getInt("UPDATEUSERID"));
				
			}
			
		}  catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		return dto;
	}

	@Override
	public CheckPadPicDto findDtoByLicenseVin(String vin)
			throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		CheckPadPicDto dto = new CheckPadPicDto();
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(" ID,");
		sb.append(" LICENSEPLATE,");
		sb.append(" VIN,");
		sb.append(" CARDTYPE,");
		sb.append(" CARIMAGES1,");
		sb.append(" CARIMAGES2,");
		sb.append(" CARIMAGES3,");
		sb.append(" CARIMAGES4,");
		sb.append(" STATUS,");
		sb.append(" INSERTTIME,");
		sb.append(" INSERTUSERID,");
		sb.append(" UPDATETIME,");
		sb.append(" UPDATEUSERID");
		sb.append(" FROM ");
		sb.append(TmsTableNames.TMS_CHECK_PAD_PIC);
		sb.append(" WHERE ");
		sb.append(" STATUS = 1 ");
		sb.append(" AND VIN=?");
		
		try {
			ps = conn.prepareStatement(sb.toString());
			ps.setString(1, vin);
			rs = ps.executeQuery();
			while(rs.next()){
				dto.setId(rs.getLong("ID"));
				dto.setLicensePlate(rs.getString("LICENSEPLATE"));
				dto.setVin(rs.getString("VIN"));
				dto.setCardType(rs.getInt("CARDTYPE"));
				dto.setCarImage1(rs.getString("CARIMAGES1"));
				dto.setCarImage2(rs.getString("CARIMAGES2"));
				dto.setCarImage3(rs.getString("CARIMAGES3"));
				dto.setCarImage4(rs.getString("CARIMAGES4"));
				dto.setStatus(rs.getInt("STATUS"));
				dto.setInsertTime(rs.getTimestamp("INSERTTIME"));
				dto.setInsertUserId(rs.getInt("INSERTUSERID"));
				dto.setUpdateTime(rs.getTimestamp("UPDATETIME"));
				dto.setUpdateUserId(rs.getInt("UPDATEUSERID"));
				
			}
			
		}  catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
		return dto;
	}

	@Override
	public void insertCheckPadPic(CheckPadPicDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StringBuffer sql = new StringBuffer("INSERT INTO ");
		sql.append(TmsTableNames.TMS_CHECK_PAD_PIC);
		sql.append("(");
		sql.append(" LICENSEPLATE,");
		sql.append(" VIN,");
		sql.append(" CARDTYPE,");
		sql.append(" CARIMAGES1,");
		sql.append(" CARIMAGES2,");
		sql.append(" CARIMAGES3,");
		sql.append(" CARIMAGES4,");
		sql.append(" STATUS,");
		sql.append(" INSERTTIME,");
		sql.append(" INSERTUSERID,");
		sql.append(" UPDATETIME,");
		sql.append(" UPDATEUSERID");
		sql.append("  ) VALUES ( ");
		sql.append(" ?,?,?,?,?,?,?,?,SYSDATE(),?,SYSDATE(),? )");
		
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, dto.getLicensePlate());
			ps.setString(2, dto.getVin());
			ps.setInt(3, dto.getCardType());
			ps.setString(4, dto.getCarImage1());
			ps.setString(5, dto.getCarImage2());
			ps.setString(6, dto.getCarImage3());
			ps.setString(7, dto.getCarImage4());
			ps.setInt(8, dto.getStatus());
			ps.setInt(9, dto.getInsertUserId());
			ps.setInt(10, dto.getUpdateUserId());
			
			ps.executeUpdate();
			
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}

	@Override
	public void updateCheckPadPic(CheckPadPicDto dto) throws TmsDaoException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StringBuffer sql = new StringBuffer("UPDATE ");
		sql.append(TmsTableNames.TMS_CHECK_PAD_PIC);
		sql.append(" SET ");
		sql.append(" LICENSEPLATE=?,");
		sql.append(" VIN=?,");
		sql.append(" CARDTYPE=?,");
		sql.append(" CARIMAGES1=?,");
		sql.append(" CARIMAGES2=?,");
		sql.append(" CARIMAGES3=?,");
		sql.append(" CARIMAGES4=?,");
		sql.append(" STATUS=?,");
		sql.append(" INSERTTIME=?,");
		sql.append(" INSERTUSERID=?,");
		sql.append(" UPDATETIME=SYSDATE(),");
		sql.append(" UPDATEUSERID=?");
		
		sql.append(" WHERE");
		sql.append(" ID=?");
		
		try {
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, dto.getLicensePlate());
			ps.setString(2, dto.getVin());
			ps.setInt(3, dto.getCardType());
			ps.setString(4, dto.getCarImage1());
			ps.setString(5, dto.getCarImage2());
			ps.setString(6, dto.getCarImage3());
			ps.setString(7, dto.getCarImage4());
			ps.setInt(8, dto.getStatus());
			ps.setTimestamp(9, dto.getInsertTime());
			ps.setInt(10, dto.getInsertUserId());
			ps.setInt(11, dto.getUpdateUserId());
			ps.setLong(12, dto.getId());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new TmsDaoException(e.toString());

		} finally {
			super.close(rs, ps);
		}
	}

}
